﻿using Common.AspNetCore;
using Common.Infrastructure;
using Microsoft.IdentityModel.Tokens;
using StatsData.Api.Models.ReportQuery;
using StatsData.Api.Utils;
using StatsData.Data.Entity;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace StatsData.Api.Repository
{
    public class ReportQueryRepository : CoreRepository
    {
        public ReportQueryRepository(IServiceProvider provider) : base(provider)
        {
        }

        public async Task<ApiPagedResult<EmpWorkHourEffectiveOutputWorkhourModel>> GetEmpEffectiveOutputWorkhourOrderPageAsync(EmpWorkHourEffectiveOutputWorkhourPageParamModel model)
        {
            if (model.OrderPageParam.PageIndex == 0)
            {
                model.OrderPageParam.PageIndex = 1;
            }

            var sqlWhere = " ";

            sqlWhere += model.ProductId != null ? $" and m.product_id = '{model.ProductId}' " : string.Empty;
            sqlWhere += model.WorkId != null ? $" and m.work_id = '{model.WorkId}' " : string.Empty;

            sqlWhere += model.BeginTime != null ? $" and m.time >=  to_date('{DateTimeUtils.ToDefaultStr(model.BeginTime)}','yyyy-mm-dd hh24:mi:ss') " : string.Empty;
            sqlWhere += model.EndTime != null ? $" and m.time <= to_date('{DateTimeUtils.ToDefaultStr(model.EndTime)}','yyyy-mm-dd hh24:mi:ss') " : string.Empty;
            sqlWhere += model.LineIds != null && model.LineIds.Any() ? $" and m.line_id in ( {string.Join(',', model.LineIds.Select(x => "'" + x + "'"))} ) " : string.Empty;
            sqlWhere += model.UserIds != null && model.UserIds.Any() ? $" and m.dir_emp_id in ( {string.Join(',', model.UserIds.Select(x => "'" + x + "'"))} ) " : string.Empty;

            var sql = $@"select * from ( 
                         select m.work_id,
                               m.line_id,
                               m.time,
                               m.product_id,
                               m.dir_emp_name,
                               m.dir_emp_code,
                               m.line_name,
                               m.line_supervisor_name,
                               m.work_code,
                               m.work_qty,
                               m.product_code,
                               m.dir_emp_id,
                               m.org_id,
                               row_number() over (ORDER BY m.time desc,m.dir_emp_code) AS line_number
                        from
                        (
                            select work_id,
                                   line_id,
                                   time,
                                   product_id,
                                   org_id,
                                   dir_emp_id,
                                   dir_emp_name,
                                   dir_emp_code,
                                   line_name,
                                   line_supervisor_name,
                                   work_code,
                                   work_qty,
                                   product_code
                            from t_bi_dir_emp_output_workhour
                            where section_code = 'ASSY'
                            union
                            select work_id,
                                   line_id,
                                   time,
                                   product_id,
                                   org_id,
                                   dir_emp_id,
                                   dir_emp_name,
                                   dir_emp_code,
                                   line_name,
                                   line_supervisor_name,
                                   work_code,
                                   work_qty,
                                   product_code
                            from t_bi_dir_emp_output_workhour
                            where section_code = 'PACK'
                        ) m
                        where  m.org_id = '{model.OrderPageParam.OrgId}' {sqlWhere} ) ";

            var countSql = "select count(1) from (" + sql + ") aa";
            var pageSql = sql + (model.OrderPageParam.PageSize == 0 ? string.Empty : $" where line_number BETWEEN {(model.OrderPageParam.PageIndex - 1) * model.OrderPageParam.PageSize + 1} AND {(model.OrderPageParam.PageIndex) * model.OrderPageParam.PageSize}  ORDER BY line_number ");

            var page = DoGetWorkHourMachineOrderPage(model, pageSql, countSql);

            if (page.Data == null)
            {
                return page;
            }

            var workIds = page.Data.Select(x => x.WorkId).Distinct().ToList();
            var lineIds = page.Data.Select(x => x.LineId).Distinct().ToList();
            var times = page.Data.Select(x => x.Time).Distinct().ToList();

            var records = await GetEmpWorkHourEffectiveOutputWorkhourAsync(workIds, lineIds, times, model.OrderPageParam.OrgId);

            foreach (var row in page.Data)
            {
                var r = records.FirstOrDefault(x => x.dir_emp_id == row.DirEmpId && x.work_id == row.WorkId && x.line_id == row.LineId && x.time == row.Time && x.section_code == "ASSY");
                row.Assy = r;

                r = records.FirstOrDefault(x => x.dir_emp_id == row.DirEmpId && x.work_id == row.WorkId && x.line_id == row.LineId && x.time == row.Time && x.section_code == "PACK");
                row.Pack = r;
            }

            return page;
        }

        private ApiPagedResult<EmpWorkHourEffectiveOutputWorkhourModel> DoGetWorkHourMachineOrderPage(EmpWorkHourEffectiveOutputWorkhourPageParamModel model, string pageSql, string countSql)
        {
            ApiPagedResult<EmpWorkHourEffectiveOutputWorkhourModel> ret = new ApiPagedResult<EmpWorkHourEffectiveOutputWorkhourModel>();
            ret.Data = new List<EmpWorkHourEffectiveOutputWorkhourModel>();
            this.Repository.ExecuteReaderByContext(
                null,
                System.Data.CommandType.Text,
                pageSql,
                null,
                (reader) =>
                {
                    while (reader.Read())
                    {
                        if (string.IsNullOrEmpty(LibSysUtils.ToString(reader.GetValue(0)).Trim()) || string.IsNullOrEmpty(LibSysUtils.ToString(reader.GetValue(1)).Trim()))
                        {
                            continue;
                        }

                        ret.Data.Add(new EmpWorkHourEffectiveOutputWorkhourModel()
                        {
                            Id = Guid.NewGuid().ToString(),
                            WorkId = LibSysUtils.ToString(reader.GetValue(0)),
                            LineId = LibSysUtils.ToString(reader.GetValue(1)),
                            Time = LibSysUtils.ToDateTime(reader.GetValue(2)).Value,
                            ProductId = LibSysUtils.ToString(reader.GetValue(3)),
                            DirEmpName = LibSysUtils.ToString(reader.GetValue(4)),
                            DirEmpCode = LibSysUtils.ToString(reader.GetValue(5)),
                            LineName = LibSysUtils.ToString(reader.GetValue(6)),
                            LineSupervisorName = LibSysUtils.ToString(reader.GetValue(7)),
                            WorkCode = LibSysUtils.ToString(reader.GetValue(8)),
                            WorkQty = LibSysUtils.ToDecimal(reader.GetValue(9)),
                            ProductCode = LibSysUtils.ToString(reader.GetValue(10)),
                            DirEmpId = LibSysUtils.ToString(reader.GetValue(11)),
                        });
                    }
                });

            if (model.OrderPageParam.PageSize != 0)
            {
                this.Repository.ExecuteReaderByContext(
                    null,
                    System.Data.CommandType.Text,
                    countSql,
                    null,
                    (reader) =>
                    {
                        if (reader.Read())
                        {
                            ret.TotalCount = LibSysUtils.ToInt(reader.GetValue(0));
                        }
                    });
            }

            if (ret.TotalCount == 0)
            {
                ret.TotalCount = ret.Data.Count;
            }

            return ret;
        }

        private Task<List<t_bi_dir_emp_output_workhour>> GetEmpWorkHourEffectiveOutputWorkhourAsync(List<string> workIds, List<string> lineIds, List<DateTime> times, string orgId)
        {
            return this.Repository.GetEntitiesAsync<t_bi_dir_emp_output_workhour>(new ApiParam(orgId), x => workIds.Contains(x.work_id) && lineIds.Contains(x.line_id) && times.Contains(x.time));
        }
    }
}
